﻿using System;
using System.Data;
using System.Collections.Generic;
using Chenxin.Lib.Business.Entity;
namespace Chenxin.Lib.Business.BLL
{
    /// <summary>
    /// 业务逻辑类ContractBaseInfo 的摘要说明。
    /// </summary>
    public class ContractBaseInfo
    {
        public ContractBaseInfo()
        { }
        #region  成员方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo model)
        {
            Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.Add(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo model)
        {
            Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.Update(model);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(Guid ContractId)
        {

            Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.Delete(ContractId);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Chenxin.Lib.Business.Entity.t_Contract_BaseInfo GetModel(Guid ContractId)
        {

            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetModel(ContractId);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Chenxin.Lib.Business.Entity.t_Contract_BaseInfo GetModelByCode(string ContractCode)
        {

            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetModelByCode(ContractCode);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strWhere);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Chenxin.Lib.Business.Entity.t_Contract_BaseInfo> GetModelList(string strWhere)
        {
            DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strWhere);
            return DataTableToList(ds.Tables[0]);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Chenxin.Lib.Business.Entity.t_Contract_BaseInfo> DataTableToList(DataTable dt)
        {
            List<Chenxin.Lib.Business.Entity.t_Contract_BaseInfo> modelList = new List<Chenxin.Lib.Business.Entity.t_Contract_BaseInfo>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Chenxin.Lib.Business.Entity.t_Contract_BaseInfo model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new Chenxin.Lib.Business.Entity.t_Contract_BaseInfo();
                    if (dt.Rows[n]["ContractId"].ToString() != "")
                    {
                        model.ContractId = new Guid(dt.Rows[n]["ContractId"].ToString());
                    }
                    if (dt.Rows[n]["ApplyId"].ToString() != "")
                    {
                        model.ApplyId = new Guid(dt.Rows[n]["ApplyId"].ToString());
                    }
                    model.ContractCode = dt.Rows[n]["ContractCode"].ToString();
                    if (dt.Rows[n]["ContractDate"].ToString() != "")
                    {
                        model.ContractDate = DateTime.Parse(dt.Rows[n]["ContractDate"].ToString());
                    }
                    model.BusinessDescribe = dt.Rows[n]["BusinessDescribe"].ToString();
                    if (dt.Rows[n]["BusinessType"].ToString() != "")
                    {
                        model.BusinessType = int.Parse(dt.Rows[n]["BusinessType"].ToString());
                    }
                    if (dt.Rows[n]["BargainType"].ToString() != "")
                    {
                        model.BargainType = int.Parse(dt.Rows[n]["BargainType"].ToString());
                    }
                    if (dt.Rows[n]["BargainUnitPrice"].ToString() != "")
                    {
                        model.BargainUnitPrice = decimal.Parse(dt.Rows[n]["BargainUnitPrice"].ToString());
                    }
                    if (dt.Rows[n]["BargainPrice"].ToString() != "")
                    {
                        model.BargainPrice = decimal.Parse(dt.Rows[n]["BargainPrice"].ToString());
                    }
                    if (dt.Rows[n]["HouseId"].ToString() != "")
                    {
                        model.HouseId = new Guid(dt.Rows[n]["HouseId"].ToString());
                    }
                    model.HouseCode = dt.Rows[n]["HouseCode"].ToString();
                    model.HouseAddress = dt.Rows[n]["HouseAddress"].ToString();
                    if (dt.Rows[n]["Area"].ToString() != "")
                    {
                        model.Area = decimal.Parse(dt.Rows[n]["Area"].ToString());
                    }
                    if (dt.Rows[n]["CommunityId"].ToString() != "")
                    {
                        model.CommunityId = new Guid(dt.Rows[n]["CommunityId"].ToString());
                    }
                    model.Community = dt.Rows[n]["Community"].ToString();
                    model.CommunityAddress = dt.Rows[n]["CommunityAddress"].ToString();
                    model.PropertyCode = dt.Rows[n]["PropertyCode"].ToString();
                    model.Region = dt.Rows[n]["Region"].ToString();
                    model.Block = dt.Rows[n]["Block"].ToString();
                    if (dt.Rows[n]["CurrentFloor"].ToString() != "")
                    {
                        model.CurrentFloor = int.Parse(dt.Rows[n]["CurrentFloor"].ToString());
                    }
                    if (dt.Rows[n]["TotalFloor"].ToString() != "")
                    {
                        model.TotalFloor = int.Parse(dt.Rows[n]["TotalFloor"].ToString());
                    }
                    model.BuildingType = dt.Rows[n]["BuildingType"].ToString();
                    model.HouseUse = dt.Rows[n]["HouseUse"].ToString();
                    model.HouseType = dt.Rows[n]["HouseType"].ToString();
                    if (dt.Rows[n]["BargainCityId"].ToString() != "")
                    {
                        model.BargainCityId = new Guid(dt.Rows[n]["BargainCityId"].ToString());
                    }
                    model.BargainCityName = dt.Rows[n]["BargainCityName"].ToString();
                    if (dt.Rows[n]["BargainCompanyId"].ToString() != "")
                    {
                        model.BargainCompanyId = new Guid(dt.Rows[n]["BargainCompanyId"].ToString());
                    }
                    model.BargainCompanyName = dt.Rows[n]["BargainCompanyName"].ToString();
                    if (dt.Rows[n]["BargainDepartmentId"].ToString() != "")
                    {
                        model.BargainDepartmentId = new Guid(dt.Rows[n]["BargainDepartmentId"].ToString());
                    }
                    model.BargainDepartmentName = dt.Rows[n]["BargainDepartmentName"].ToString();
                    if (dt.Rows[n]["BargainUserId"].ToString() != "")
                    {
                        model.BargainUserId = new Guid(dt.Rows[n]["BargainUserId"].ToString());
                    }
                    model.BargainUserName = dt.Rows[n]["BargainUserName"].ToString();
                    if (dt.Rows[n]["VenderCityId"].ToString() != "")
                    {
                        model.VenderCityId = new Guid(dt.Rows[n]["VenderCityId"].ToString());
                    }
                    model.VenderCityName = dt.Rows[n]["VenderCityName"].ToString();
                    if (dt.Rows[n]["VenderCompanyId"].ToString() != "")
                    {
                        model.VenderCompanyId = new Guid(dt.Rows[n]["VenderCompanyId"].ToString());
                    }
                    model.VenderCompanyName = dt.Rows[n]["VenderCompanyName"].ToString();
                    if (dt.Rows[n]["VenderDepartId"].ToString() != "")
                    {
                        model.VenderDepartId = new Guid(dt.Rows[n]["VenderDepartId"].ToString());
                    }
                    model.VenderDepartName = dt.Rows[n]["VenderDepartName"].ToString();
                    if (dt.Rows[n]["VenderUserId"].ToString() != "")
                    {
                        model.VenderUserId = new Guid(dt.Rows[n]["VenderUserId"].ToString());
                    }
                    model.VenderUserName = dt.Rows[n]["VenderUserName"].ToString();
                    if (dt.Rows[n]["VenderId"].ToString() != "")
                    {
                        model.VenderId = new Guid(dt.Rows[n]["VenderId"].ToString());
                    }
                    model.VenderName = dt.Rows[n]["VenderName"].ToString();
                    model.VenderNo = dt.Rows[n]["VenderNo"].ToString();
                    model.VenderPhone = dt.Rows[n]["VenderPhone"].ToString();
                    model.VenderMobile = dt.Rows[n]["VenderMobile"].ToString();
                    if (dt.Rows[n]["BuyerCityId"].ToString() != "")
                    {
                        model.BuyerCityId = new Guid(dt.Rows[n]["BuyerCityId"].ToString());
                    }
                    model.BuyerCityName = dt.Rows[n]["BuyerCityName"].ToString();
                    if (dt.Rows[n]["BuyerCompanyId"].ToString() != "")
                    {
                        model.BuyerCompanyId = new Guid(dt.Rows[n]["BuyerCompanyId"].ToString());
                    }
                    model.BuyerCompanyName = dt.Rows[n]["BuyerCompanyName"].ToString();
                    if (dt.Rows[n]["BuyerDepartId"].ToString() != "")
                    {
                        model.BuyerDepartId = new Guid(dt.Rows[n]["BuyerDepartId"].ToString());
                    }
                    model.BuyerDepartName = dt.Rows[n]["BuyerDepartName"].ToString();
                    if (dt.Rows[n]["BuyerUserId"].ToString() != "")
                    {
                        model.BuyerUserId = new Guid(dt.Rows[n]["BuyerUserId"].ToString());
                    }
                    model.BuyerUserName = dt.Rows[n]["BuyerUserName"].ToString();
                    if (dt.Rows[n]["BuyerId"].ToString() != "")
                    {
                        model.BuyerId = new Guid(dt.Rows[n]["BuyerId"].ToString());
                    }
                    model.BuyerName = dt.Rows[n]["BuyerName"].ToString();
                    model.BuyerNo = dt.Rows[n]["BuyerNo"].ToString();
                    model.BuyerPhone = dt.Rows[n]["BuyerPhone"].ToString();
                    model.BuyerMobile = dt.Rows[n]["BuyerMobile"].ToString();
                    if (dt.Rows[n]["VStanCommision"].ToString() != "")
                    {
                        model.VStanCommision = decimal.Parse(dt.Rows[n]["VStanCommision"].ToString());
                    }
                    if (dt.Rows[n]["BStanCommision"].ToString() != "")
                    {
                        model.BStanCommision = decimal.Parse(dt.Rows[n]["BStanCommision"].ToString());
                    }
                    if (dt.Rows[n]["VRealCommision"].ToString() != "")
                    {
                        model.VRealCommision = decimal.Parse(dt.Rows[n]["VRealCommision"].ToString());
                    }
                    if (dt.Rows[n]["BRealCommision"].ToString() != "")
                    {
                        model.BRealCommision = decimal.Parse(dt.Rows[n]["BRealCommision"].ToString());
                    }
                    if (dt.Rows[n]["VCommision"].ToString() != "")
                    {
                        model.VCommision = decimal.Parse(dt.Rows[n]["VCommision"].ToString());
                    }
                    if (dt.Rows[n]["BCommision"].ToString() != "")
                    {
                        model.BCommision = decimal.Parse(dt.Rows[n]["BCommision"].ToString());
                    }
                    if (dt.Rows[n]["VConsulting"].ToString() != "")
                    {
                        model.VConsulting = decimal.Parse(dt.Rows[n]["VConsulting"].ToString());
                    }
                    if (dt.Rows[n]["BConsulting"].ToString() != "")
                    {
                        model.BConsulting = decimal.Parse(dt.Rows[n]["BConsulting"].ToString());
                    }
                    if (dt.Rows[n]["AppEmployeeId"].ToString() != "")
                    {
                        model.AppEmployeeId = new Guid(dt.Rows[n]["AppEmployeeId"].ToString());
                    }
                    model.AppEmployee = dt.Rows[n]["AppEmployee"].ToString();
                    if (dt.Rows[n]["AppDepartId"].ToString() != "")
                    {
                        model.AppDepartId = new Guid(dt.Rows[n]["AppDepartId"].ToString());
                    }
                    model.AppDepart = dt.Rows[n]["AppDepart"].ToString();
                    if (dt.Rows[n]["AppCompanyId"].ToString() != "")
                    {
                        model.AppCompanyId = new Guid(dt.Rows[n]["AppCompanyId"].ToString());
                    }
                    model.AppCompany = dt.Rows[n]["AppCompany"].ToString();
                    if (dt.Rows[n]["AppCityId"].ToString() != "")
                    {
                        model.AppCityId = new Guid(dt.Rows[n]["AppCityId"].ToString());
                    }
                    model.AppCity = dt.Rows[n]["AppCity"].ToString();
                    if (dt.Rows[n]["ConfirmEmplyeeId"].ToString() != "")
                    {
                        model.ConfirmEmplyeeId = new Guid(dt.Rows[n]["ConfirmEmplyeeId"].ToString());
                    }
                    model.ConfirmEmplyee = dt.Rows[n]["ConfirmEmplyee"].ToString();
                    if (dt.Rows[n]["ConfirmDepartId"].ToString() != "")
                    {
                        model.ConfirmDepartId = new Guid(dt.Rows[n]["ConfirmDepartId"].ToString());
                    }
                    model.ConfirmDepart = dt.Rows[n]["ConfirmDepart"].ToString();
                    if (dt.Rows[n]["ConfirmCompanyId"].ToString() != "")
                    {
                        model.ConfirmCompanyId = new Guid(dt.Rows[n]["ConfirmCompanyId"].ToString());
                    }
                    model.ConfirmCompany = dt.Rows[n]["ConfirmCompany"].ToString();
                    if (dt.Rows[n]["ConfirmCityId"].ToString() != "")
                    {
                        model.ConfirmCityId = new Guid(dt.Rows[n]["ConfirmCityId"].ToString());
                    }
                    model.ConfirmCity = dt.Rows[n]["ConfirmCity"].ToString();
                    if (dt.Rows[n]["AddTime"].ToString() != "")
                    {
                        model.AddTime = DateTime.Parse(dt.Rows[n]["AddTime"].ToString());
                    }
                    if (dt.Rows[n]["UpdateCityId"].ToString() != "")
                    {
                        model.UpdateCityId = new Guid(dt.Rows[n]["UpdateCityId"].ToString());
                    }
                    model.UpdateCityName = dt.Rows[n]["UpdateCityName"].ToString();
                    if (dt.Rows[n]["UpdateCompanyId"].ToString() != "")
                    {
                        model.UpdateCompanyId = new Guid(dt.Rows[n]["UpdateCompanyId"].ToString());
                    }
                    model.UpdateCompanyName = dt.Rows[n]["UpdateCompanyName"].ToString();
                    if (dt.Rows[n]["UpdateDepartmentId"].ToString() != "")
                    {
                        model.UpdateDepartmentId = new Guid(dt.Rows[n]["UpdateDepartmentId"].ToString());
                    }
                    model.UpdateDepartmentName = dt.Rows[n]["UpdateDepartmentName"].ToString();
                    if (dt.Rows[n]["UpdateUserId"].ToString() != "")
                    {
                        model.UpdateUserId = new Guid(dt.Rows[n]["UpdateUserId"].ToString());
                    }
                    model.UpdateUserName = dt.Rows[n]["UpdateUserName"].ToString();
                    if (dt.Rows[n]["UpdateTime"].ToString() != "")
                    {
                        model.UpdateTime = DateTime.Parse(dt.Rows[n]["UpdateTime"].ToString());
                    }
                    if (dt.Rows[n]["bArchivement"].ToString() != "")
                    {
                        if ((dt.Rows[n]["bArchivement"].ToString() == "1") || (dt.Rows[n]["bArchivement"].ToString().ToLower() == "true"))
                        {
                            model.bArchivement = true;
                        }
                        else
                        {
                            model.bArchivement = false;
                        }
                    }
                    if (dt.Rows[n]["ArchivementDate"].ToString() != "")
                    {
                        model.ArchivementDate = DateTime.Parse(dt.Rows[n]["ArchivementDate"].ToString());
                    }
                    if (dt.Rows[n]["ArchivementYear"].ToString() != "")
                    {
                        model.ArchivementYear = int.Parse(dt.Rows[n]["ArchivementYear"].ToString());
                    }
                    if (dt.Rows[n]["ArchivementMonth"].ToString() != "")
                    {
                        model.ArchivementMonth = int.Parse(dt.Rows[n]["ArchivementMonth"].ToString());
                    }
                    if (dt.Rows[n]["LastCreateArchDate"].ToString() != "")
                    {
                        model.LastCreateArchDate = DateTime.Parse(dt.Rows[n]["LastCreateArchDate"].ToString());
                    }
                    if (dt.Rows[n]["CreateArchEmpId"].ToString() != "")
                    {
                        model.CreateArchEmpId = new Guid(dt.Rows[n]["CreateArchEmpId"].ToString());
                    }
                    model.CreateArchEmp = dt.Rows[n]["CreateArchEmp"].ToString();
                    if (dt.Rows[n]["LockStatus"].ToString() != "")
                    {
                        model.LockStatus = int.Parse(dt.Rows[n]["LockStatus"].ToString());
                    }
                    if (dt.Rows[n]["Status"].ToString() != "")
                    {
                        model.Status = int.Parse(dt.Rows[n]["Status"].ToString());
                    }
                    model.PointRemark = dt.Rows[n]["PointRemark"].ToString();
                    if (dt.Rows[n]["ContractCityId"].ToString() != "")
                    {
                        model.ContractCityId = new Guid(dt.Rows[n]["ContractCityId"].ToString());
                    }
                    model.ContractCityName = dt.Rows[n]["ContractCityName"].ToString();
                    if (dt.Rows[n]["ContractCompanyId"].ToString() != "")
                    {
                        model.ContractCompanyId = new Guid(dt.Rows[n]["ContractCompanyId"].ToString());
                    }
                    model.ContractCompanyName = dt.Rows[n]["ContractCompanyName"].ToString();
                    if (dt.Rows[n]["ContractDepartmentId"].ToString() != "")
                    {
                        model.ContractDepartmentId = new Guid(dt.Rows[n]["ContractDepartmentId"].ToString());
                    }
                    model.ContractDepartmentName = dt.Rows[n]["ContractDepartmentName"].ToString();
                    if (dt.Rows[n]["ContractUserId"].ToString() != "")
                    {
                        model.ContractUserId = new Guid(dt.Rows[n]["ContractUserId"].ToString());
                    }
                    model.ContractUserName = dt.Rows[n]["ContractUserName"].ToString();
                    model.CompleteYear = dt.Rows[n]["CompleteYear"].ToString();
                    model.Fitment = dt.Rows[n]["Fitment"].ToString();
                    model.ContractUserName = dt.Rows[n]["ContractUserName"].ToString();
                    if (dt.Rows[n]["LitigationStatus"].ToString() != "")
                    {
                        model.LitigationStatus = int.Parse(dt.Rows[n]["LitigationStatus"].ToString());
                    }
                    if (dt.Rows[n]["HasFollow"].ToString() != "")
                    {
                        if ((dt.Rows[n]["HasFollow"].ToString() == "1") || (dt.Rows[n]["HasFollow"].ToString().ToLower() == "true"))
                        {
                            model.HasFollow = true;
                        }
                        else
                        {
                            model.HasFollow = false;
                        }
                    }
                    if (dt.Rows[n]["HouseProperty"].ToString() != "")
                    {
                        model.HouseProperty = int.Parse(dt.Rows[n]["HouseProperty"].ToString());
                    }
                    model.CustomerSource = dt.Rows[n]["CustomerSource"].ToString();
                    if (dt.Rows[n]["VSTDiscount"].ToString() != "")
                    {
                        model.VSTDiscount = decimal.Parse(dt.Rows[n]["VSTDiscount"].ToString());
                    }
                    if (dt.Rows[n]["BSTDiscount"].ToString() != "")
                    {
                        model.BSTDiscount = decimal.Parse(dt.Rows[n]["BSTDiscount"].ToString());
                    }
                    model.STMemo = dt.Rows[n]["STMemo"].ToString();
                    if (dt.Rows[n]["FixDiscount"].ToString() != "")
                    {
                        model.FixDiscount = decimal.Parse(dt.Rows[n]["FixDiscount"].ToString());
                    }
                    if (dt.Rows[n]["DiscountRate"].ToString() != "")
                    {
                        model.DiscountRate = decimal.Parse(dt.Rows[n]["DiscountRate"].ToString());
                    }
                    if (dt.Rows[n]["RateDate"].ToString() != "")
                    {
                        model.RateDate = DateTime.Parse(dt.Rows[n]["RateDate"].ToString());
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetAllList()
        {
            return GetList("");
        }

        public DataSet GetListByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;

            string TableName = "t_Contract_BaseInfo";
            string OrderField = "ContractDate DESC";
            string FieldList = "ContractCode," +
                                    "ContractDate," +
                                    "HouseAddress," +
                                    "Area," +
                                    "BargainPrice," +
                                    "ArchivementDate," +
                                    "HouseCode," +
                                    "BusinessType," +
                                    "BargainCompanyName," +
                                    "VenderDepartName," +
                                    "BuyerDepartName," +
                                    "VenderUserName," +
                                    "BuyerUserName," +
                                    "VPoint," +
                                    "BPoint," +
                                    "VCommision," +
                                    "BCommision," +
                                    "VCommisionGeted," +
                                    "BCommisionGeted";

            string TableFieldList = "ContractCode," +
                                    "ContractDate," +
                                    "HouseAddress," +
                                    "Area," +
                                    "BargainPrice," +
                                    "CONVERT(VARCHAR(7) , ArchivementDate, 102 ) As ArchivementDate," +
                                    "HouseCode," +
                                    "(Case When BusinessType = 10 Then '买卖' When BusinessType = 20 Then '租赁' Else '' End) As BusinessType," +
                                    "BargainCompanyName," +
                                    "(STUFF((SELECT '/' + t_Contract_PointAllotInfo.Depart FROM t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo =10 FOR XML PATH('')),1,1,'')) As VenderDepartName," +
                                    "(STUFF((SELECT '/' + t_Contract_PointAllotInfo.Depart FROM t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo =20 FOR XML PATH('')),1,1,'')) As BuyerDepartName," +
                                    "(STUFF((SELECT '/' + t_Contract_PointAllotInfo.Employee FROM t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo =10 FOR XML PATH('')),1,1,'')) As VenderUserName," +
                                    "(STUFF((SELECT '/' + t_Contract_PointAllotInfo.Employee FROM t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo =20 FOR XML PATH('')),1,1,'')) As BuyerUserName," +
                                    "(STUFF((SELECT '/' + CAST(Point AS NVARCHAR) FROM t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo =10 FOR XML PATH('')),1,1,'')) As VPoint," +
                                    "(STUFF((SELECT '/' + CAST(Point AS NVARCHAR) FROM t_Contract_PointAllotInfo WHERE t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo =20 FOR XML PATH('')),1,1,'')) As BPoint," +
                                    "(SELECT VRealCommisionFinal FROM t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId = t_Contract_BaseInfo.ContractId) As VCommision," +
                                    "(SELECT BRealCommisionFinal FROM t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId = t_Contract_BaseInfo.ContractId) As BCommision," +
                                    "(STUFF((SELECT '/' + CAST( CAST((VRealCommisionFinal * Point/10) AS DECIMAL(18,2)) AS NVARCHAR) FROM t_Contract_PointAllotInfo,t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId = t_Contract_BaseInfo.ContractId AND t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo = 10 FOR XML PATH('')),1,1,'')) As VCommisionGeted," +
                                    "(STUFF((SELECT '/' + CAST( CAST((VRealCommisionFinal * Point/10) AS DECIMAL(18,2)) AS NVARCHAR) FROM t_Contract_PointAllotInfo,t_Contract_CommisionInfo WHERE t_Contract_CommisionInfo.ContractId = t_Contract_BaseInfo.ContractId AND t_Contract_BaseInfo.ContractId =t_Contract_PointAllotInfo.ContractId AND BuyerOrVenderInfo = 20 FOR XML PATH('')),1,1,'')) As BCommisionGeted";

            strWhere.Append(" 1=1 ");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And ContractDate>='{0}'", dicParams["StartTime"]);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And ContractDate<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
            }

            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (ContractCode Like '%{0}%' Or HouseAddress Like '%{0}%')", dicParams["Keyword"]);
            }

            if (dicParams.ContainsKey("BusinessType"))
            {
                strWhere.AppendFormat(" And BusinessType = {0}", dicParams["BusinessType"]);
            }
            else
            {
                strWhere.AppendFormat(" And BusinessType <= {0}", 20);
            }

            if (dicParams.ContainsKey("AreaS"))
            {
                strWhere.AppendFormat(" And Area>={0}", dicParams["AreaS"]);
            }
            if (dicParams.ContainsKey("AreaE"))
            {
                strWhere.AppendFormat(" And Area<={0}", dicParams["AreaE"]);
            }
            if (dicParams.ContainsKey("BargainPriceS"))
            {
                strWhere.AppendFormat(" And BargainPrice>={0}", dicParams["BargainPriceS"]);
            }
            if (dicParams.ContainsKey("BargainPriceE"))
            {
                strWhere.AppendFormat(" And BargainPrice<={0}", dicParams["BargainPriceE"]);
            }

            if (dicParams.ContainsKey("ArchivementDate"))
            {
                strWhere.AppendFormat(" And DATEDIFF(mm,ArchivementDate,'{0}') = 0", dicParams["ArchivementDate"] + "-01");
            }

            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And (VenderCityId = '{0}' OR BuyerCityId = '{0}') ", dicParams["CityId"]);

            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And (VenderCompanyId = '{0}' OR BuyerCompanyId = '{0}')", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And (Exists(Select 1 From v_Hr_DepartmentInfo Where DepartId = VenderDepartId And DepartSrc Like '{0}%')", AreaInfo.DepartSrc);
                strWhere.AppendFormat(" Or Exists(Select 1 From v_Hr_DepartmentInfo Where DepartId = BuyerDepartId And DepartSrc Like '{0}%')", AreaInfo.DepartSrc);
                strWhere.AppendFormat(" Or Exists(Select 1 From v_Hr_DepartmentInfo,t_Contract_PointAllotInfo Where t_Contract_PointAllotInfo.ContractId = t_Contract_BaseInfo.ContractId And v_Hr_DepartmentInfo.DepartId = t_Contract_PointAllotInfo.DepartId And DepartSrc Like '{0}%'))", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And (VenderDepartId = '{0}'", dicParams["DepartId"]);
                strWhere.AppendFormat(" Or BuyerDepartId = '{0}'", dicParams["DepartId"]);
                strWhere.AppendFormat(" Or Exists(Select 1 From t_Contract_PointAllotInfo Where t_Contract_PointAllotInfo.ContractId = t_Contract_BaseInfo.ContractId And DepartId = '{0}'))", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And ContractUserId = '{0}'", dicParams["UserId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.AppendFormat(" And ContractUserId = '{0}'", dicParams["CurUserId"]);
            }

            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(PageIndex, PageSize, OrderField, TableName, strWhere.ToString(), FieldList, TableFieldList, true);
        }


        //个人排行榜导出全部
        public DataSet GetPersonalRankExportAll(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();

            string TableName = "v_Contract_CommisionDetailInfo";
            string GroupField = "EmployeeId,Employee ";
            string OrderField = "SUM(ApportionCommision) DESC";

            string TableFieldList = "EmployeeId," +
                                    "Employee," +
                                    "(Select BelongDepart From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As Depart," +
                                    "dbo.FN_GetBussinessByEMPID(EmployeeId) As AreaName," +
                                    "SUM(Point) As Point," +
                                    "SUM(ApportionAR) As AllCommision," +
                                    "SUM(ApportionCommision) As PureCommision," +
                                    "RANK() OVER(ORDER BY SUM(ApportionCommision) DESC) As RANK_PURE," +
                                    "(Select UserCode From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As UserCode," +
                                    "(Select Position From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As Position," +
                                    "(Select DATEDIFF(mm,ComeDate,GETDATE()) From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As WorkAgeBegin";

            //strWhere.Append(" Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Left Join Chenxin_Main.dbo.HR_Rel_Office On(OfficeName = Position) Where PostName='业务员' And UserId = EmployeeId)");
            strWhere.Append(" bArchivement = 1");
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate>='{0}'", dicParams["StartTime"]);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
            }

            if (dicParams.ContainsKey("UserCode"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And UserCode = '{0}')", dicParams["UserCode"]);
            }

            if (dicParams.ContainsKey("BusinessType"))
            {
                if (dicParams["BusinessType"] == "60")
                {
                    strWhere.Append(" And BusinessType <50");
                }
                else
                {
                    strWhere.AppendFormat(" And BusinessType = {0}", dicParams["BusinessType"]);
                }
            }

            if (dicParams.ContainsKey("Status"))
            {
                if (dicParams["Status"] != "0")
                {
                    strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And Status = {0})", dicParams["Status"]);
                }
            }


            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And CityId = '{0}')", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And CompanyId = '{0}')", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And (Exists(Select 1 From v_Hr_DepartmentInfo V Where V.DepartId = v_Contract_CommisionDetailInfo.DepartId And V.DepartSrc Like '{0}%'))", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And DepartId = '{0}'", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And EmployeeId = '{0}'", dicParams["UserId"]);
            }
            if (dicParams.ContainsKey("CurDepartId"))
            {
                strWhere.AppendFormat(" And DepartId in (select DepartId from t_Business_EmpSetting where Userid= '{0}' and SearchPersonalRank=1) ", dicParams["CurDepartId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.AppendFormat(" And EmployeeId = '{0}'", dicParams["CurUserId"]);
            }

            string strSql = "select " + TableFieldList + " from " + TableName + " where " + strWhere.ToString() + " group by " + GroupField + " order by " + OrderField;

            DataSet ds = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
            return ds;
        }

        /// <summary>
        /// 获取个人业绩排行榜
        /// </summary>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public DataSet GetPersonalRankByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;

            string TableName = "v_Contract_CommisionDetailInfo";
            string GroupField = "EmployeeId,Employee ";
            string OrderField = "SUM(ApportionCommision) DESC";
            string FieldList = "EmployeeId," +
                                "Employee," +
                                "Depart," +
                                "AreaName," +
                                "Point," +
                                "AllCommision," +
                                "PureCommision," +
                                "RANK_PURE,UserCode,Position,WorkAgeBegin";

            string TableFieldList = "EmployeeId," +
                                    "Employee," +
                                    "(Select BelongDepart From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As Depart," +
                                    "dbo.FN_GetBussinessByEMPID(EmployeeId) As AreaName," +
                                    "SUM(Point) As Point," +
                                    "SUM(ApportionAR) As AllCommision," +
                                    "SUM(ApportionCommision) As PureCommision," +
                                    "RANK() OVER(ORDER BY SUM(ApportionCommision) DESC) As RANK_PURE," +
                                    "(Select UserCode From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As UserCode," +
                                    "(Select Position From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As Position," +
                                    "(Select DATEDIFF(mm,ComeDate,GETDATE()) From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId) As WorkAgeBegin";

            //strWhere.Append(" Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Left Join Chenxin_Main.dbo.HR_Rel_Office On(OfficeName = Position) Where PostName='业务员' And UserId = EmployeeId)");
            strWhere.Append(" bArchivement = 1");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate>='{0}'", dicParams["StartTime"]);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
            }

            if (dicParams.ContainsKey("UserCode"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And UserCode = '{0}')", dicParams["UserCode"]);
            }

            if (dicParams.ContainsKey("BusinessType"))
            {
                if (dicParams["BusinessType"] == "60")
                {
                    strWhere.AppendFormat(" And BusinessType < 50");
                }
                else
                {
                    strWhere.AppendFormat(" And BusinessType = {0}", dicParams["BusinessType"]);
                }
            }

            if (dicParams.ContainsKey("Status"))
            {
                if (dicParams["Status"] != "0")
                {
                    strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And Status = {0})", dicParams["Status"]);
                }
            }


            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And CityId = '{0}')", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And CompanyId = '{0}')", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And (Exists(Select 1 From v_Hr_DepartmentInfo V Where V.DepartId = v_Contract_CommisionDetailInfo.DepartId And V.DepartSrc Like '{0}%'))", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And DepartId = '{0}'", dicParams["DepartId"]);
            }
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And EmployeeId = '{0}'", dicParams["UserId"]);
            }
            if (dicParams.ContainsKey("CurDepartId"))
            {
                strWhere.AppendFormat(" And DepartId in (select DepartId from t_Business_EmpSetting where Userid= '{0}' and SearchPersonalRank=1) ", dicParams["CurDepartId"]);
            }
            if (dicParams.ContainsKey("CurUserId"))
            {
                strWhere.AppendFormat(" And EmployeeId = '{0}'", dicParams["CurUserId"]);
            }

            DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetPartList(PageIndex, PageSize, OrderField, TableName, strWhere.ToString(), GroupField, FieldList, TableFieldList, true);

            //添加总合计
            string strSql = "select SUM(Point) As Point,SUM(ApportionAR) As AllCommision,SUM(ApportionCommision) As PureCommision from " + TableName + " where " + strWhere.ToString();
            DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
            System.Data.DataRow dr = ds.Tables[0].NewRow();
            dr["UserCode"] = "总合计";
            dr["Point"] = ds_count.Tables[0].Rows[0]["Point"];
            dr["AllCommision"] = ds_count.Tables[0].Rows[0]["AllCommision"];
            dr["PureCommision"] = ds_count.Tables[0].Rows[0]["PureCommision"];
            ds.Tables[0].Rows.Add(dr);

            return ds;
        }

        /// <summary>
        /// 获取个人业绩详情列表
        /// </summary>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public DataSet GetPersonalAchievementByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;

            string TableName = "v_Contract_CommisionDetailInfo";
            string OrderField = "ContractDate DESC";
            string FieldList = "ContractCode," +
                                "ContractDate," +
                                "HouseAddress," +
                                "Area," +
                                "BargainPrice," +
                                "AllCommision," +
                                "AllConsulting," +
                                "PureCommision," +
                                "ApportionAR," +
                                "ApportionCommision," +
                                "ApportionConsulting," +
                                "ArchivementDate," +
                                "ContractCompanyName";

            string TableFieldList = "ContractCode," +
                                    "ContractDate," +
                                    "HouseAddress," +
                                    "Area," +
                                    "BargainPrice," +
                                    "AllCommision," +
                                    "AllConsulting," +
                                    "PureCommision," +
                                    "ApportionAR," +
                                    "ApportionCommision," +
                                    "ApportionConsulting," +
                                    "ArchivementDate," +
                                    "ContractCompanyName";

            //strWhere.Append(" Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Left Join Chenxin_Main.dbo.HR_Rel_Office On(OfficeName = Position) Where PostName='业务员' And UserId = EmployeeId)");
            strWhere.Append(" bArchivement = 1");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate>='{0}'", dicParams["StartTime"]);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
            }

            //if (dicParams.ContainsKey("UserCode"))
            //{
            //    strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And UserCode = '{0}')", dicParams["UserCode"]);
            //}

            if (dicParams.ContainsKey("BusinessType"))
            {
                if (dicParams["BusinessType"] == "60")
                {
                    strWhere.Append(" And BusinessType < 50");
                }
                else
                {
                    strWhere.AppendFormat(" And BusinessType = {0}", dicParams["BusinessType"]);
                }
            }

            //if (dicParams.ContainsKey("Status"))
            //{
            //    if (dicParams["Status"] != "0")
            //    {
            //        strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And Status = {0})", dicParams["Status"]);
            //    }
            //}
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And EmployeeId = '{0}'", dicParams["UserId"]);
            }

            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(PageIndex, PageSize, OrderField, TableName, strWhere.ToString(), FieldList, TableFieldList, true);
        }


        /// <summary>
        /// 获取房款收支平衡表导出全部数据
        /// </summary>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public DataSet GetSaleBalanceExportAll(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            string SearchObj = string.Empty;

            string TableName = "t_Contract_BaseInfo Left Join t_Contract_CommisionInfo On(t_Contract_CommisionInfo.ContractId = t_Contract_BaseInfo.ContractId)";
            string OrderField = "ContractDate DESC";

            string TableFieldList = "dbo.FN_GetBussinessByDepartID(ContractDepartmentId) As AreaName," +
                                    "ContractDepartmentName As Depart," +
                                    "ContractUserName As UserName," +
                                    "ContractCode," +
                                    "REPLACE(CONVERT(Varchar(10),ContractDate,111),'/','-') as ContractDate," +
                                    "VenderName," +
                                    "BuyerName," +
                                    "HouseAddress," +
                                    "Area," +
                                    "BargainPrice," +
                                    "CanGetSaleMoney," +
                                    "GetedSaleMoney," +
                                    "PayedSaleMoney," +
                                    "(GetedSaleMoney - PayedSaleMoney) As RemainSaleMoney";

            strWhere.Append(" 1 = 1");
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("DateType"))
            {
                if (dicParams["DateType"].Equals("10"))
                {
                    SearchObj = "ContractDate";
                }
                if (dicParams.ContainsKey("StartTime"))
                {
                    strWhere.AppendFormat(" And {0}>='{1}'", SearchObj, dicParams["StartTime"]);
                }
                if (dicParams.ContainsKey("EndTime"))
                {
                    strWhere.AppendFormat(" And {0}<'{1}'", SearchObj, DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
                }
            }

            if (dicParams.ContainsKey("BalanceStatus"))
            {
                if (dicParams["BalanceStatus"] == "-1")
                {
                    strWhere.Append(" And (GetedSaleMoney > PayedSaleMoney Or GetedSaleMoney < PayedSaleMoney)");
                }
                else
                {
                    strWhere.Append(" And GetedSaleMoney = PayedSaleMoney ");
                }
            }
            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (ContractCode Like '%{0}%' Or HouseAddress Like '%{0}%') ", dicParams["Keyword"]);
            }


            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And ContractCityId = '{0}'", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And ContractCompanyId = '{0}'", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And (Exists(Select 1 From v_Hr_DepartmentInfo V Where V.DepartId = ContractDepartmentId And V.DepartSrc Like '{0}%'))", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And ContractDepartmentId = '{0}'", dicParams["DepartId"]);
            }

            string strSql = "select " + TableFieldList + " from " + TableName + " where " + strWhere.ToString() + " order by " + OrderField;

            return Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
        }

        /// <summary>
        /// 获取房款收支平衡表
        /// </summary>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public DataSet GetSaleBalanceByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;
            string SearchObj = string.Empty;

            string TableName = "t_Contract_BaseInfo Left Join t_Contract_CommisionInfo On(t_Contract_CommisionInfo.ContractId = t_Contract_BaseInfo.ContractId)";
            string OrderField = "ContractDate DESC";
            string FieldList = "AreaName," +
                                "Depart," +
                                "UserName," +
                                "ContractCode," +
                                "ContractDate," +
                                "VenderName," +
                                "BuyerName," +
                                "HouseAddress," +
                                "Area," +
                                "BargainPrice," +
                                "CanGetSaleMoney," +
                                "GetedSaleMoney," +
                                "PayedSaleMoney," +
                                "RemainSaleMoney";

            string TableFieldList = "dbo.FN_GetBussinessByDepartID(ContractDepartmentId) As AreaName," +
                                    "ContractDepartmentName As Depart," +
                                    "ContractUserName As UserName," +
                                    "ContractCode," +
                                    "ContractDate," +
                                    "VenderName," +
                                    "BuyerName," +
                                    "HouseAddress," +
                                    "Area," +
                                    "BargainPrice," +
                                    "CanGetSaleMoney," +
                                    "GetedSaleMoney," +
                                    "PayedSaleMoney," +
                                    "(GetedSaleMoney - PayedSaleMoney) As RemainSaleMoney";

            strWhere.Append(" 1 = 1");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("DateType"))
            {
                if (dicParams["DateType"].Equals("10"))
                {
                    SearchObj = "ContractDate";
                }
                if (dicParams.ContainsKey("StartTime"))
                {
                    strWhere.AppendFormat(" And {0}>='{1}'", SearchObj, dicParams["StartTime"]);
                }
                if (dicParams.ContainsKey("EndTime"))
                {
                    strWhere.AppendFormat(" And {0}<'{1}'", SearchObj, DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
                }
            }

            if (dicParams.ContainsKey("BalanceStatus"))
            {
                if (dicParams["BalanceStatus"] == "-1")
                {
                    strWhere.Append(" And (GetedSaleMoney > PayedSaleMoney Or GetedSaleMoney < PayedSaleMoney)");
                }
                else
                {
                    strWhere.Append(" And GetedSaleMoney = PayedSaleMoney ");
                }
            }
            if (dicParams.ContainsKey("Keyword"))
            {
                strWhere.AppendFormat(" And (ContractCode Like '%{0}%' Or HouseAddress Like '%{0}%') ", dicParams["Keyword"]);
            }


            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And ContractCityId = '{0}'", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And ContractCompanyId = '{0}'", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And (Exists(Select 1 From v_Hr_DepartmentInfo V Where V.DepartId = ContractDepartmentId And V.DepartSrc Like '{0}%'))", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And ContractDepartmentId = '{0}'", dicParams["DepartId"]);
            }

            DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(PageIndex, PageSize, OrderField, TableName, strWhere.ToString(), FieldList, TableFieldList, true);

            //*************获取总合计数*****************
            string strSql = "select sum(isnull(CanGetSaleMoney,0)) as CanGetSaleMoney,sum(isnull(GetedSaleMoney,0)) as GetedSaleMoney,sum(isnull(PayedSaleMoney,0)) as PayedSaleMoney,sum(isnull((GetedSaleMoney - PayedSaleMoney),0)) as RemainSaleMoney from " + TableName + " where " + strWhere.ToString();
            DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
            System.Data.DataRow dr = ds.Tables[0].NewRow();
            dr["AreaName"] = "总合计";
            dr["CanGetSaleMoney"] = ds_count.Tables[0].Rows[0]["CanGetSaleMoney"];
            dr["GetedSaleMoney"] = ds_count.Tables[0].Rows[0]["GetedSaleMoney"];
            dr["PayedSaleMoney"] = ds_count.Tables[0].Rows[0]["PayedSaleMoney"];
            dr["RemainSaleMoney"] = ds_count.Tables[0].Rows[0]["RemainSaleMoney"];
            ds.Tables[0].Rows.Add(dr);
            //*****************************************

            return ds;
        }


        /// <summary>
        /// 获取店长以上级业绩表
        /// </summary>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public DataSet GetMangerCollAchievementByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;
            string UserObjectId = "DeparterManagerId";
            string UserObject = "DeparterManagerId,DeparterManagerName";
            string UserObjectSel = "DeparterManagerId As ObjectId,DeparterManagerName As ObjectName,";


            if (dicParams.ContainsKey("UserObjectId"))
            {
                UserObjectId = dicParams["UserObjectId"];

                switch (UserObjectId)
                {
                    case "DeparterManagerId":
                        UserObject = "DeparterManagerId,DeparterManagerName";
                        UserObjectSel = "DeparterManagerId As ObjectId,DeparterManagerName As ObjectName,";
                        break;
                    case "MajordomoId":
                        UserObject = "MajordomoId,MajordomoName";
                        UserObjectSel = "MajordomoId As ObjectId,MajordomoName As ObjectName,";
                        break;
                    case "RegionManagerId":
                        UserObject = "RegionManagerId,RegionManagerName";
                        UserObjectSel = "RegionManagerId As ObjectId,RegionManagerName As ObjectName,";
                        break;
                    case "MangerId":
                        UserObject = "MangerId,MangerName";
                        UserObjectSel = "MangerId As ObjectId,MangerName As ObjectName,";
                        break;
                    default:
                        break;
                }
            }

            string TableName = "v_Contract_CommisionDetailInfo Left Join Chenxin_Main.dbo.Hr_UserInfo On(" + UserObjectId + "= UserId) ";
            string OrderField = "UserCode ";
            string GroupField = "BelongDepartId,BelongDepart,UserCode," + UserObject;
            string FieldList = "ObjectId," +
                               "ObjectName," +
                               "UserCode," +
                                "DepartName," +
                                "AreaName," +
                                "SalePoint," +
                                "RentPoint," +
                                "LoosePoint," +
                                "OtherPoint," +
                                "SaleCommision," +
                                "RentCommision," +
                                "LooseCommision," +
                                "OtherCommision," +
                                "NewHousePoint," +
                                "NewHouseCommision," +
                                "TotalCommision";

            string TableFieldList = UserObjectSel +
                                   "UserCode," +
                                   "BelongDepart As DepartName," +
                                   "Chenxin_Main.dbo.FN_GetBussinessByDepartID(BelongDepartId) As AreaName," +
                                   "Sum(Case When BusinessType = 10 Then Point Else 0 End) As SalePoint," +
                                   "Sum(Case When BusinessType = 20 Then Point Else 0 End) As RentPoint," +
                                   "Sum(Case When BusinessType = 30 Then Point Else 0 End) As LoosePoint," +
                                   "Sum(Case When BusinessType = 40 Then Point Else 0 End) As OtherPoint," +
                                   "Sum(Case When BusinessType = 10 Then ApportionCommision Else 0 End) As SaleCommision," +
                                   "Sum(Case When BusinessType = 20 Then ApportionCommision Else 0 End) As RentCommision," +
                                   "Sum(Case When BusinessType = 30 Then ApportionCommision Else 0 End) As LooseCommision," +
                                   "Sum(Case When BusinessType = 40 Then ApportionCommision Else 0 End) As OtherCommision," +
                                   "Sum(Case When BusinessType = 50 Then Point Else 0 End) As NewHousePoint," +
                                   "Sum(Case When BusinessType = 50 Then ApportionCommision Else 0 End) As NewHouseCommision," +
                                   "Sum(ApportionCommision) As TotalCommision";

            string fieldtemp ="Sum(Case When BusinessType = 10 Then Point Else 0 End) As SalePoint," +
                                   "Sum(Case When BusinessType = 20 Then Point Else 0 End) As RentPoint," +
                                   "Sum(Case When BusinessType = 30 Then Point Else 0 End) As LoosePoint," +
                                   "Sum(Case When BusinessType = 40 Then Point Else 0 End) As OtherPoint," +
                                   "Sum(Case When BusinessType = 10 Then ApportionCommision Else 0 End) As SaleCommision," +
                                   "Sum(Case When BusinessType = 20 Then ApportionCommision Else 0 End) As RentCommision," +
                                   "Sum(Case When BusinessType = 30 Then ApportionCommision Else 0 End) As LooseCommision," +
                                   "Sum(Case When BusinessType = 40 Then ApportionCommision Else 0 End) As OtherCommision," +
                                   "Sum(Case When BusinessType = 50 Then Point Else 0 End) As NewHousePoint," +
                                   "Sum(Case When BusinessType = 50 Then ApportionCommision Else 0 End) As NewHouseCommision," +
                                   "Sum(ApportionCommision) As TotalCommision";

            //strWhere.Append(" Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Left Join Chenxin_Main.dbo.HR_Rel_Office On(OfficeName = Position) Where PostName='业务员' And UserId = EmployeeId)");
            strWhere.Append(" bArchivement = 1 ");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate>='{0}'", dicParams["StartTime"]);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
            }
            if (dicParams.ContainsKey("BusinessType"))
            {
                strWhere.AppendFormat(" And BusinessType ='{0}'", dicParams["BusinessType"]);
            }
            if (dicParams.ContainsKey("CityId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And CityId = '{0}')", dicParams["CityId"]);
            }
            if (dicParams.ContainsKey("CompanyId"))
            {
                strWhere.AppendFormat(" And Exists(Select 1 From Chenxin_Main.dbo.Hr_UserInfo Where UserId = EmployeeId And CompanyId = '{0}')", dicParams["CompanyId"]);
            }
            if (dicParams.ContainsKey("AreaId"))
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo AreaInfo = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, dicParams["AreaId"]);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                AreaInfo = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    AreaInfo = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(new Guid(dicParams["AreaId"]));
                    if (AreaInfo != null) cs.Set(strCachkey, AreaInfo, DateTime.Now.AddDays(1));
                }
                strWhere.AppendFormat(" And (Exists(Select 1 From v_Hr_DepartmentInfo V Where V.DepartId = v_Contract_CommisionDetailInfo.DepartId And V.DepartSrc Like '{0}%'))", AreaInfo.DepartSrc);
            }
            if (dicParams.ContainsKey("DepartId"))
            {
                strWhere.AppendFormat(" And v_Contract_CommisionDetailInfo.DepartId = '{0}'", dicParams["DepartId"]);
            }

            DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetPartList(PageIndex, PageSize, OrderField, TableName, strWhere.ToString(), GroupField, FieldList, TableFieldList, true);

            //*************获取总合计数*****************
            string str = string.Format("select {2} from {0} where {1} ", TableName, strWhere.ToString(), fieldtemp);
            DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(str);
            System.Data.DataRow dr = ds.Tables[0].NewRow();
            dr["UserCode"] = "总合计";
            dr["TotalCommision"] = ds_count.Tables[0].Rows[0]["TotalCommision"];
            dr["SalePoint"] = ds_count.Tables[0].Rows[0]["SalePoint"];
            dr["RentPoint"] = ds_count.Tables[0].Rows[0]["RentPoint"];
            dr["LoosePoint"] = ds_count.Tables[0].Rows[0]["LoosePoint"];
            dr["OtherPoint"] = ds_count.Tables[0].Rows[0]["OtherPoint"];
            dr["SaleCommision"] = ds_count.Tables[0].Rows[0]["SaleCommision"];
            dr["RentCommision"] = ds_count.Tables[0].Rows[0]["RentCommision"];
            dr["LooseCommision"] = ds_count.Tables[0].Rows[0]["LooseCommision"];
            dr["OtherCommision"] = ds_count.Tables[0].Rows[0]["OtherCommision"];
            ds.Tables[0].Rows.Add(dr);
            //*****************************************


            return ds;
        }

        /// <summary>
        /// 获取店长以上业绩详情列表
        /// </summary>
        /// <param name="dicParams"></param>
        /// <returns></returns>
        public DataSet GetMangerAchievementByParams(Dictionary<string, string> dicParams)
        {
            System.Text.StringBuilder strWhere = new System.Text.StringBuilder();
            int PageSize = 10;
            int PageIndex = 1;
            string UserObjectId = "DeparterManagerId";

            string TableName = "v_Contract_CommisionDetailInfo";
            string OrderField = "ContractDate DESC";
            string FieldList = "ContractCode," +
                                "ContractDate," +
                                "HouseAddress," +
                                "Area," +
                                "BargainPrice," +
                                "AllCommision," +
                                "AllConsulting," +
                                "PureCommision," +
                                "ApportionAR," +
                                "ApportionCommision," +
                                "ApportionConsulting," +
                                "ArchivementDate," +
                                "ContractCompanyName";

            string TableFieldList = "ContractCode," +
                                    "ContractDate," +
                                    "HouseAddress," +
                                    "Area," +
                                    "BargainPrice," +
                                    "AllCommision," +
                                    "AllConsulting," +
                                    "PureCommision," +
                                    "ApportionAR," +
                                    "ApportionCommision," +
                                    "ApportionConsulting," +
                                    "ArchivementDate," +
                                    "ContractCompanyName";

            strWhere.Append(" bArchivement = 1");
            if (dicParams.ContainsKey("PageSize"))
            {
                PageSize = Convert.ToInt32(dicParams["PageSize"]);
            }
            if (dicParams.ContainsKey("PageIndex"))
            {
                PageIndex = Convert.ToInt32(dicParams["PageIndex"]) + 1;
            }
            if (dicParams.ContainsKey("Sort"))
            {
                OrderField = dicParams["Sort"];
            }
            if (dicParams.ContainsKey("StartTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate>='{0}'", dicParams["StartTime"]);
            }
            if (dicParams.ContainsKey("EndTime"))
            {
                strWhere.AppendFormat(" And ArchivementDate<'{0}'", DateTime.Parse(dicParams["EndTime"]).AddDays(1).ToString("yyyy-MM-dd"));
            }

            if (dicParams.ContainsKey("UserObjectId"))
            {
                UserObjectId = dicParams["UserObjectId"];
            }
            if (dicParams.ContainsKey("UserId"))
            {
                strWhere.AppendFormat(" And {1} = '{0}'", dicParams["UserId"], UserObjectId);
            }

            return Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(PageIndex, PageSize, OrderField, TableName, strWhere.ToString(), FieldList, TableFieldList, true);
        }

        /// <summary>
        /// 将下家客户信息作为一条客户信息保存到流通库中
        /// </summary>
        /// <param name="contractModel"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public static bool AddHouseNewOwnerInfo(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo contractModel, out string strMessage)
        {
            strMessage = "";
            try
            {
                //在流通系统中添加下家客户
                Chenxin.Lib.House.Entity.Customer_BaseInfo customerBaseInfo = new Chenxin.Lib.House.Entity.Customer_BaseInfo();

                customerBaseInfo.CustomerId = contractModel.BuyerId;
                customerBaseInfo.CompanyId = contractModel.BuyerCompanyId;
                customerBaseInfo.CompanyName = contractModel.BuyerCompanyName;
                customerBaseInfo.CityId = contractModel.BuyerCityId;
                customerBaseInfo.CityName = contractModel.BuyerCityName;
                customerBaseInfo.Adddate = System.DateTime.Now;
                customerBaseInfo.AddPersonId = contractModel.BuyerUserId;
                customerBaseInfo.AddPerson = contractModel.BuyerUserName;
                customerBaseInfo.AddDepartId = contractModel.BuyerDepartId;
                customerBaseInfo.AddDepart = contractModel.BuyerDepartName;
                customerBaseInfo.OprPersonId = contractModel.BuyerUserId;
                customerBaseInfo.OprPerson = contractModel.BuyerUserName;
                customerBaseInfo.OprDepartId = contractModel.BuyerDepartId;
                customerBaseInfo.OprDepart = contractModel.BuyerDepartName;
                customerBaseInfo.LastTraceDay = DateTime.Now;
                customerBaseInfo.CustomerKind = 40;
                customerBaseInfo.PubLevel = 0;
                customerBaseInfo.CustomerCode = Chenxin.Lib.House.DAL.Customer_BaseInfo.GetCustomerCode(contractModel.BuyerUserId);
                customerBaseInfo.CustomerName = contractModel.BuyerName;
                customerBaseInfo.CertificateType = "身份证";
                customerBaseInfo.CertificateNo = contractModel.BuyerNo;
                customerBaseInfo.Phone = contractModel.BuyerPhone;
                customerBaseInfo.Mobile = contractModel.BuyerMobile;
                customerBaseInfo.Address = contractModel.HouseAddress;

                Chenxin.Lib.House.DAL.Customer_BaseInfo.Delete(contractModel.BuyerId);
                Chenxin.Lib.House.DAL.Customer_BaseInfo.Add(customerBaseInfo);

                return true;
            }
            catch (Exception ex)
            {
                strMessage = "添加客户(新业主)信息失败！" + ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 更新成交房源的业主信息及所有人信息
        /// </summary>
        /// <param name="contractModel"></param>
        /// <param name="strMessages"></param>
        /// <returns></returns>
        public static bool UpdateHouseInfo(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo contractModel, out string strMessages)
        {
            strMessages = string.Empty;

            try
            {
                //更新房源的业主信息及所属人信息
                Chenxin.Lib.House.Entity.House_BaseInfo house = Chenxin.Lib.House.DAL.House_BaseInfo.GetModel(contractModel.HouseId);
                if (house == null) return false;
                Chenxin.Lib.Passport.Entity.Hr_UserInfo user = Chenxin.Lib.Passport.DAL.Hr_UserInfo.GetEntityById(contractModel.BuyerUserId);
                if (user == null)
                {
                    strMessages = "下家业务员信息丢失！";
                    return false;
                }
                if (contractModel.BusinessType == 10)//发生买卖业务
                {
                    //更改所属人为下家业务员
                    house.Status = 20;
                    house.OprDepart = user.DepartName;
                    house.OprDepartId = user.DepartId;
                    house.OprPerson = user.UserName;
                    house.OprPersonId = user.UserId;
                    house.CustomerId = contractModel.BuyerId;//业主默认更改为下家客户
                }
                else if (house.BusinessKind == 20)//若租赁房源租赁成交
                {
                    house.Status = 20;
                }
                
                house.ServiceTime = DateTime.Now;
                house.UpdateTime = DateTime.Now;
                Chenxin.Lib.House.DAL.House_BaseInfo.Update(house);
                //清除缓存
                //string strCachKey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_HOUSEINFO, contractModel.HouseId);
                //Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                //cs.Set(strCachKey, house, DateTime.Now.AddHours(1));

                return true;
            }
            catch (Exception ex)
            {
                strMessages = "更新房源信息过程出错：" + ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 保存成交客户信息到成交库的客户表中
        /// </summary>
        /// <param name="contractModel"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public static bool AddContractCustomerInfo(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo contractModel, out string strMessage)
        {
            strMessage = "";
            try
            {
                //添加上家客户
                Chenxin.Lib.Business.Entity.t_Customer_BaseInfo customerBaseInfo = new Chenxin.Lib.Business.Entity.t_Customer_BaseInfo();
                customerBaseInfo.CustomerId = contractModel.VenderId;
                customerBaseInfo.CustomerName = contractModel.VenderName;
                customerBaseInfo.Adddate = DateTime.Now;
                customerBaseInfo.CompanyId = contractModel.VenderCompanyId;
                customerBaseInfo.CompanyName = contractModel.VenderCompanyName;
                customerBaseInfo.AddPersonId = contractModel.VenderUserId;
                customerBaseInfo.AddPerson = contractModel.VenderUserName;
                customerBaseInfo.AddDepartId = contractModel.VenderDepartId;
                customerBaseInfo.AddDepart = contractModel.VenderDepartName;
                customerBaseInfo.OprPersonId = contractModel.VenderUserId;
                customerBaseInfo.OprPerson = contractModel.VenderUserName;
                customerBaseInfo.OprDepartId = contractModel.VenderDepartId;
                customerBaseInfo.OprDepart = contractModel.VenderDepartName;
                customerBaseInfo.LastTraceDay = DateTime.Now;
                customerBaseInfo.CustomerKind = 40;
                customerBaseInfo.CityId = contractModel.VenderCityId;
                customerBaseInfo.CityName = contractModel.VenderCityName;
                customerBaseInfo.PubLevel = 0;
                customerBaseInfo.CustomerCode = "";

                customerBaseInfo.CertificateType = "身份证";
                customerBaseInfo.CertificateNo = contractModel.VenderNo;
                customerBaseInfo.Phone = contractModel.VenderPhone;
                customerBaseInfo.Mobile = contractModel.VenderMobile;
                customerBaseInfo.Address = contractModel.HouseAddress;
                Chenxin.Lib.Business.DAL.t_Customer_BaseInfo.Delete(contractModel.VenderId);
                Chenxin.Lib.Business.DAL.t_Customer_BaseInfo.Add(customerBaseInfo);

                //添加下家客户
                customerBaseInfo.CustomerId = contractModel.BuyerId;
                customerBaseInfo.Adddate = DateTime.Now;
                customerBaseInfo.CompanyId = contractModel.BuyerCompanyId;
                customerBaseInfo.CompanyName = contractModel.BuyerCompanyName;
                customerBaseInfo.AddPersonId = contractModel.BuyerUserId;
                customerBaseInfo.AddPerson = contractModel.BuyerUserName;
                customerBaseInfo.AddDepartId = contractModel.BuyerDepartId;
                customerBaseInfo.AddDepart = contractModel.BuyerDepartName;
                customerBaseInfo.OprPersonId = contractModel.BuyerUserId;
                customerBaseInfo.OprPerson = contractModel.BuyerUserName;
                customerBaseInfo.OprDepartId = contractModel.BuyerDepartId;
                customerBaseInfo.OprDepart = contractModel.BuyerDepartName;
                customerBaseInfo.LastTraceDay = DateTime.Now;
                customerBaseInfo.CustomerKind = 40;
                customerBaseInfo.CityId = contractModel.BuyerCityId;
                customerBaseInfo.CityName = contractModel.BuyerCityName;
                customerBaseInfo.PubLevel = 0;
                customerBaseInfo.CustomerCode = "";
                customerBaseInfo.CustomerName = contractModel.BuyerName;
                customerBaseInfo.CertificateType = "身份证";
                customerBaseInfo.CertificateNo = contractModel.BuyerNo;
                customerBaseInfo.Phone = contractModel.BuyerPhone;
                customerBaseInfo.Mobile = contractModel.BuyerMobile;
                customerBaseInfo.Address = contractModel.HouseAddress;
                Chenxin.Lib.Business.DAL.t_Customer_BaseInfo.Delete(contractModel.BuyerId);
                Chenxin.Lib.Business.DAL.t_Customer_BaseInfo.Add(customerBaseInfo);

                return true;
            }
            catch (Exception ex)
            {
                strMessage = "添加上下家客户信息失败！" + ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 添加合同的佣金信息
        /// </summary>
        /// <param name="contractModel"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public static bool AddContractCommissionInfo(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo contractModel, out string strMessage)
        {
            strMessage = "";
            try
            {
                Chenxin.Lib.Business.Entity.t_Contract_CommisionInfo commisionInfo = new Chenxin.Lib.Business.Entity.t_Contract_CommisionInfo();
                commisionInfo.ContractId = contractModel.ContractId;
                commisionInfo.VRealCommisionFinal = (contractModel.VRealCommision.HasValue) ? contractModel.VRealCommision.Value:0;
                commisionInfo.BRealCommisionFinal = (contractModel.BRealCommision.HasValue) ?contractModel.BRealCommision.Value:0;
                commisionInfo.VCommisionFinal = (contractModel.VCommision.HasValue)?contractModel.VCommision:0;
                commisionInfo.BCommisionFinal = (contractModel.BCommision.HasValue)?contractModel.BCommision:0;
                commisionInfo.VConsultingFinal = (contractModel.VConsulting.HasValue)?contractModel.VConsulting:0;
                commisionInfo.BConsultingFinal = (contractModel.BConsulting.HasValue)?contractModel.BConsulting:0;
                commisionInfo.VCommisionGeted = 0;
                commisionInfo.BCommisionGeted = 0;
                commisionInfo.VCommisionGetedPayed = 0;
                commisionInfo.BCommisionGetedPayed = 0;
                commisionInfo.VConsultingPayed = 0;
                commisionInfo.BConsultingPayed = 0;
                commisionInfo.CanGetSaleMoney = 0;
                commisionInfo.GetedSaleMoney = 0;
                commisionInfo.PayedSaleMoney = 0;
                commisionInfo.CanGetTax = 0;
                commisionInfo.GetedTax = 0;
                commisionInfo.PayedTax = 0;
                commisionInfo.FixDiscountFinal = (contractModel.FixDiscount.HasValue) ? contractModel.FixDiscount : 0;
                commisionInfo.VSTDiscountFinal = (contractModel.VSTDiscount.HasValue) ? contractModel.VSTDiscount : 0;
                commisionInfo.BSTDiscountFinal = (contractModel.BSTDiscount.HasValue) ? contractModel.BSTDiscount : 0;
                commisionInfo.DiscountRateFinal = (contractModel.DiscountRate.HasValue) ? contractModel.DiscountRate : 1;
                Chenxin.Lib.Business.DAL.t_Contract_CommisionInfo.Add(commisionInfo);

                return true;
            }
            catch (Exception ex)
            {
                strMessage = "保存成交佣金信息失败:" + ex.Message;
                return false;
            }

        }

        /// <summary>
        /// 更新合同的佣金信息
        /// </summary>
        /// <param name="contractModel"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public static bool UpdateContractCommissionInfo(Chenxin.Lib.Business.Entity.t_Contract_BaseInfo contractModel, out string strMessage)
        {
            strMessage = "";
            try
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                sb.Append("Update t_Contract_CommisionInfo Set ");
                sb.AppendFormat("VCommisionFinal = {0},",(contractModel.VCommision.HasValue) ? contractModel.VCommision: 0);
                sb.AppendFormat("BCommisionFinal = {0},", (contractModel.BCommision.HasValue) ? contractModel.BCommision : 0);
                sb.AppendFormat("FixDiscountFinal={0},", (contractModel.FixDiscount.HasValue) ? contractModel.FixDiscount : 0);
                sb.Append("VRealCommisionFinal = dbo.udf_CalculateCommisionfinalValue(contractId,10),");
                sb.Append("BRealCommisionFinal = dbo.udf_CalculateCommisionfinalValue(contractId,20),");
                sb.Append("VConsultingFinal = dbo.udf_CalculateCommisionfinalValue(contractId,30),");
                sb.Append("BConsultingFinal = dbo.udf_CalculateCommisionfinalValue(contractId,40)");
                sb.AppendFormat(" where contractId = '{0}'", contractModel.ContractId);


                Chenxin.Lib.Business.DAL.t_Contract_CommisionInfo.ExcuteSql(sb.ToString());

                return true;
            }
            catch (Exception ex)
            {
                strMessage = "更新成交佣金信息失败:" + ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 生成成交编号
        /// </summary>
        /// <param name="CompanyId">合同所属公司ID</param>
        /// <param name="BusinessType">成交类型</param>
        /// <returns></returns>
        public static string GenerateCode(Guid CompanyId, int BusinessType)
        {
            //生成成交编号
            return Chenxin.Lib.Business.DAL.ContractApplyInfo.GenerateCode(CompanyId, BusinessType);
        }

        /// <summary>
        /// 重新计算指定合同的佣金预扣除情况
        /// </summary>
        /// <param name="contractId"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public static bool CaculateFinanceDeductInfo(Guid contractId, out string strMessage)
        {
            try
            {
                strMessage = "";
                Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.CaculateFinanceDeductInfo(contractId);
                return true;
            }
            catch (Exception ex)
            {
                strMessage = "计算实收抵扣情况失败:" + ex.Message;
                return false;
            }
        }

        #endregion  成员方法
    }
}

